clear all
	
*) Bring in Data
	cd "D:\Taylor_JAERE_2020_DONE"
	import delimited using BagBan_60stores.txt, delimiters("|") bindquotes(nobind) stripquotes(yes)
						
	rename v1 store_id
	rename v2 division_id
	rename v3 promo_week_id
	rename v4 upc_id
	rename v5 upc_desc
	rename v6 category_id
	rename v7 hh_id
	rename v8 txn_id
	rename v9 txn_date
	rename v10 txn_time
	rename v11 cashier_nbr
	rename v12 reg_nbr 
	rename v13 item_qty
	rename v14 price
	rename v15 price_paid

*)Relabel variables
	label variable store_id "Store ID"
	label variable division_id "Division ID"
	label variable promo_week_id "Promo Week ID"
	label variable upc_id "UPC ID"
	label variable upc_desc "UPC Descr"
	label variable category_id "Category ID"
	label variable hh_id "HH ID"
	label variable txn_id "Txn ID"
	label variable txn_date "Txn Date"
	label variable txn_time "Txn Time"
	label variable cashier_nbr "Cashier Number"
	label variable reg_nbr "Register Number"
	label variable item_qty "Item Qty"
	label variable price "Gross Price"
	label variable price_paid "Price Paid"	

*)Generate Bag variables
	gen paper_bag = 0
	replace paper_bag = 1 if upc_id == 49941020201
	gen reus_bag = 0
	replace reus_bag = 1 if category_id == 7585

*)Merge in category names
	merge m:1 category_id using map_categ_name.dta
	drop if _merge == 2

*)Create 12 department groups
	replace dept_id = 1 if _merge == 	1 &	((category_id >=3300 & category_id <= 3399) |	(category_id >=8900 & category_id <= 8999))	
	replace dept_id = 2 if _merge == 	1 &	((category_id >=8200 & category_id <= 8399) |	(category_id >=9401 & category_id <= 9599))	
	replace dept_id = 3 if _merge == 	1 &	(category_id >=3601 & category_id <= 3999) 		
	replace dept_id = 4 if _merge == 	1 &	(category_id >=8500 & category_id <= 8599) 		
	replace dept_id = 5 if _merge == 	1 &	(category_id >=8100 & category_id <= 8199) 		
	replace dept_id = 6 if _merge == 	1 &	(category_id >=4001 & category_id <= 4899) 		
	replace dept_id = 7 if _merge == 	1 &	(category_id >=8600 & category_id <= 8899)				
	replace dept_id = 9 if _merge == 	1 &	(category_id >=8400 & category_id <= 8499) 		
	replace dept_id = 10 if _merge == 	1 &	(category_id >=3201 & category_id <= 3299) 		
	replace dept_id = 11 if _merge == 	1 &	(( category_id < 3000) |	(category_id >=9201 & category_id <= 9299) |	(category_id >=9601 & category_id <= 9799) )
	replace dept_id = 12 if _merge == 	1 &	(category_id >=6501 & category_id <= 6599) 						
	replace dept_id = 8 if _merge == 	1 &	dept_id == .									
	tab dept_id, gen(DEPT_)

*)Generate variables for the amount of expenditures, total scans.			
	gen expenditure_paid = price_paid /*When summed to the transaction level, this will be the amount paid.*/

	gen tot_scan = abs(item_qty) /*tot_scan will be a positive number. When summed to the transaction level, it will include items purchased, items returned, and non-"item" items (such as mark-downs and bag fees.*/
	replace tot_scan = 1 if item_qty == 0

	drop upc_id upc_desc category_id _merge dept_id
	collapse (sum) item_qty expenditure_paid tot_scan paper_bag reus_bag (max) DEPT_*, ///
					by(store_id division_id promo_week_id hh_id txn_id txn_date txn_time cashier_nbr reg_nbr ) 

*) Generate year, hour, min, month variables
	gen str4 year =substr(txn_time,1,4)
	gen str2 hour =substr(txn_time,12,2)
	gen str2 min =substr(txn_time,15,2)
	gen str2 month = substr(txn_time,6, 2)
	gen str2 day = substr(txn_time,9, 2)
	
	destring year, replace
	destring hour, replace
	destring min, replace
	destring month, replace	
	destring day, replace	
							
*)Store 3083 has typos. Txn_date says one date but txn_time says another date. Drop this store.
	drop if store_id == 3083

*) Drop Christmas day because not all stores are open on Christmas day
	drop if month == 12 & day == 25

*)Create date_group
	sort year month day
	egen date_group = group(year month day)
	
*)Self-checkout registers
	tab reg_nbr					

	gen sc 			= 0
					forvalues xx = 51(1)54 {
					replace sc = 1 if reg_nbr == `xx'
					}
					forvalues xx = 93(1)99 {
					replace sc = 1 if reg_nbr == `xx'
					}			
					
*)generate txn_mins
	tab hour
	gen tt1 = 0
	replace tt1 = 60 if hour == 14
	replace tt1 = 120 if hour == 15
	replace tt1 = 180 if hour == 16
	
	gen tt2 = tt1 + min 
	
	sort store_id year month day reg_nbr cashier_nbr tt2 txn_id
	egen tt3 = group(store_id year month day reg_nbr cashier_nbr)
	bysort tt3: gen txn_mins = tt2- tt2[_n-1] /*Time stamp is at end of transaction (receipt).*/
	
	*) Dont need transaction times for registers that are not checkout. 
	replace txn_mins = . if (reg_nbr > 20 & reg_nbr < 51) | (reg_nbr > 54 & reg_nbr < 93) 

	*) Drop transactions with more than 200 items
	replace txn_mins = . if tot_scan > 200	
	sum txn_mins
	
	replace txn_mins = . if txn_mins > 20 &txn_mins!=. 
	replace txn_mins = . if tot_scan > 140 & ((reg_nbr < 55 & reg_nbr > 50) | reg_nbr == 93 | reg_nbr == 94 | reg_nbr == 99) &txn_mins!=.
	replace txn_mins = . if txn_mins > (5 + tot_scan*0.075) & reg_nbr < 21 &txn_mins!=.
	replace txn_mins = . if txn_mins > (10 + tot_scan*0.25) & ((reg_nbr < 55 & reg_nbr > 50) | reg_nbr == 93 | reg_nbr == 94 | reg_nbr == 99) &txn_mins!=.
	sum txn_mins	
				
*) Drop unecessary registers				
	drop if reg_nbr == 9999
	drop if reg_nbr == 39	
	drop if store_id == 313 & (reg_nbr == 7 | reg_nbr == 9)															
	drop if store_id == 653 & (reg_nbr == 9)																													
	drop if store_id == 676 & (reg_nbr == 8)															
	drop if store_id == 910 & (reg_nbr == 11 | reg_nbr == 12)	
	drop if store_id == 976 & (reg_nbr == 8)	
	drop if store_id == 997 & (reg_nbr == 9)	
	drop if store_id == 1204 & (reg_nbr == 94)
	drop if store_id == 1205 & (reg_nbr == 10)
	drop if store_id == 1722 & (reg_nbr == 10)
	drop if store_id == 2089 & (reg_nbr == 8)
	drop if store_id == 2314 & (reg_nbr == 9)
	drop if store_id == 2317 & (reg_nbr == 9)

*) Generate registers opened (by type) and number of transactions per 10 minute interval
	gen byte dec_min = 0 if min >=0 & min <10 
	replace dec_min = 1 if min >=10 & min <20 
	replace dec_min = 2 if min >=20 & min <30 
	replace dec_min = 3 if min >=30 & min <40 
	replace dec_min = 4 if min >=40 & min <50 
	replace dec_min = 5 if min >=50 & min <60 
	
	sort store_id year month day reg_nbr hour dec_min 

	egen dte_shift = group(store_id year month day)
	egen reg_day_id = group(dte_shift reg_nbr)	
	egen cashreg_open_tag = tag(reg_day_id) if reg_nbr<20 & txn_mins != .
	egen sc_open_tag = tag(reg_day_id)	if sc==1 & txn_mins != .	 
	
	bysort dte_shift: egen cashreg_open = sum(cashreg_open_tag) 
	bysort dte_shift: egen sc_open = sum(sc_open_tag) 	
	drop dte_shift reg_day_id cashreg_open_tag sc_open_tag

	gen regs_to_keep =  (reg_nbr<20| (reg_nbr > 50 & reg_nbr < 55) | reg_nbr > 92)	
	egen dte_shift = group(store_id year month day regs_to_keep)
	bysort dte_shift: gen store_day_TRANS = _N				
	drop dte_shift regs_to_keep
		
*) Gen TREAT & POST Variables
	gen POST = 0 
	gen TREAT = 0	 

	*Store ID 309
	replace POST = 1 if year > 2012 & store_id == 309
	replace TREAT = 1 if store_id == 309

	*Store ID 313
	replace POST = 1 if year > 2012 & store_id == 313
	replace POST = 1 if year ==2012 & month > 6 & store_id ==313
	replace POST = 1 if year == 2012 & month ==6 & day > 22 & store_id == 313
	replace TREAT = 1 if store_id == 313

	*Store ID 640
	replace POST = 1 if year > 2012 & store_id == 640
	replace POST = 1 if year ==2012 & month > 3 & store_id ==640
	replace POST = 1 if year == 2012 & month ==3 & day > 23 & store_id == 640
	replace TREAT = 1 if store_id == 640

	*Store ID 669
	replace POST = 1 if year > 2012 & store_id == 669
	replace TREAT = 1 if store_id == 669

	*Store ID 676
	replace POST = 1 if year > 2012 & store_id == 676
	replace TREAT = 1 if store_id == 676

	*Store ID 687
	replace POST = 1 if year > 2012 & store_id == 687
	replace TREAT = 1 if store_id == 687

	*Store ID 706 
	replace POST = 1 if year > 2013 & store_id == 706
	replace POST = 1 if year ==2013 & month > 2 & store_id ==706
	replace POST = 1 if year == 2013 & month ==2 & day > 2 & store_id == 706
	replace TREAT = 1 if store_id == 706

	*Store ID 747
	replace POST = 1 if year > 2013 & store_id == 747
	replace POST = 1 if year ==2013 & month > 9 & store_id ==747
	replace TREAT = 1 if store_id == 747

	*Store ID 786
	replace POST = 1 if year > 2012 & store_id == 786
	replace POST = 1 if year ==2012 & month > 3 & store_id ==786
	replace POST = 1 if year == 2012 & month ==3 & day > 23 & store_id == 786
	replace TREAT = 1 if store_id == 786

	*Store ID 788
	replace POST = 1 if year == 2014 & store_id == 788
	replace TREAT = 1 if store_id == 788

	*Store ID 790
	replace POST = 1 if year > 2012 & store_id == 790
	replace TREAT = 1 if store_id == 790

	*Store ID 908
	replace POST = 1 if year > 2012 & store_id == 908
	replace TREAT = 1 if store_id == 908

	*Store ID 910
	replace POST = 1 if year > 2012 & store_id == 910
	replace TREAT = 1 if store_id == 910

	*Store ID 970
	replace POST = 1 if year > 2013 & store_id == 970
	replace POST = 1 if year ==2013 & month > 6 & store_id == 970
	replace POST = 1 if year == 2013 & month ==6 & day > 6 & store_id == 970
	replace TREAT = 1 if store_id == 970

	*Store ID 971
	replace POST = 1 if year > 2012 & store_id == 971
	replace TREAT = 1 if store_id == 971

	*Store ID 987
	replace POST = 1 if year > 2011 & store_id == 987
	replace TREAT = 1 if store_id == 987
		
	*Store ID 997
	replace POST = 1 if year > 2011 & store_id == 997
	replace TREAT = 1 if store_id == 997

	*Store ID 999
	replace POST = 1 if year > 2013 & store_id == 999
	replace POST = 1 if year ==2013 & month > 4 & store_id == 999
	replace POST = 1 if year == 2013 & month ==4 & day > 26 & store_id == 999
	replace TREAT = 1 if store_id == 999

	*Store ID 1108
	replace POST = 1 if year > 2013 & store_id == 1108
	replace POST = 1 if year ==2013 & month > 4 & store_id == 1108
	replace POST = 1 if year == 2013 & month ==4 & day > 26 & store_id == 1108
	replace TREAT = 1 if store_id == 1108

	*Store ID 1138
	replace POST = 1 if year > 2013 & store_id == 1138
	replace POST = 1 if year ==2013 & month > 4 & store_id == 1138
	replace POST = 1 if year == 2013 & month ==4 & day > 26 & store_id == 1138
	replace TREAT = 1 if store_id == 1138

	*Store ID 1196
	replace POST = 1 if year > 2012 & store_id == 1196
	replace POST = 1 if year ==2012 & month > 6 & store_id ==1196
	replace POST = 1 if year == 2012 & month ==6 & day > 22 & store_id == 1196
	replace TREAT = 1 if store_id == 1196

	*Store ID 1224
	replace POST = 1 if year > 2012 & store_id == 1224
	replace TREAT = 1 if store_id == 1224

	*Store ID 1245
	replace POST = 1 if year ==2014 & month > 2 & store_id ==1245
	replace POST = 1 if year == 2014 & month ==2 & day > 7 & store_id == 1245
	replace TREAT = 1 if store_id == 1245

	*Store ID 1476
	replace POST = 1 if year > 2011 & store_id == 1476
	replace TREAT = 1 if store_id == 1476
		
	*Store ID 1483
	replace POST = 1 if year > 2011 & store_id == 1483
	replace TREAT = 1 if store_id == 1483

	*Store ID  1583
	replace POST = 1 if year > 2012 & store_id == 1583
	replace POST = 1 if year ==2013 & month > 1 & store_id == 1583
	replace POST = 1 if year == 2013 & month ==1 & day > 11 & store_id == 1583
	replace TREAT = 1 if store_id == 1583

	*Store ID 1953
	replace POST = 1 if year > 2012 & store_id == 1953
	replace TREAT = 1 if store_id == 1953 

	*Store ID 2078
	replace POST = 1 if year > 2013 & store_id == 2078
	replace TREAT = 1 if store_id == 2078
		
	*Store ID 2089
	replace POST = 1 if year > 2012 & store_id == 2089
	replace TREAT = 1 if store_id == 2089

	*Store ID 2139
	replace POST = 1 if year > 2012 & store_id == 2139
	replace POST = 1 if year == 2012 & month > 6 & store_id == 2139
	replace TREAT = 1 if store_id == 2139

	*Store ID 2212
	replace POST = 1 if year == 2014 & store_id == 2212
	replace POST = 1 if year == 2013 & month ==12 & day >27 & store_id == 2212
	replace TREAT = 1 if store_id == 2212
		
	*Store ID 2264
	replace POST = 1 if year > 2012 & store_id == 2264
	replace POST = 1 if year == 2012 & month > 6 & store_id == 2264
	replace TREAT = 1 if store_id ==  2264

	*Store ID 2306
	replace POST = 1 if year > 2012 & store_id == 2306
	replace POST = 1 if year == 2012 & month > 9 & store_id == 2306
	replace TREAT = 1 if store_id ==  2306

	*Store ID 2312
	replace POST = 1 if year > 2012 & store_id == 2312
	replace POST = 1 if year == 2012 & month > 9 & store_id == 2312
	replace TREAT = 1 if store_id ==  2312

	*Store ID 2314
	replace POST = 1 if year > 2012 & store_id == 2314
	replace POST = 1 if year == 2012 & month > 9 & store_id == 2314
	replace TREAT = 1 if store_id ==  2314

	*Store ID 2317
	replace POST = 1 if year > 2012 & store_id == 2317
	replace POST = 1 if year == 2012 & month > 9 & store_id == 2317
	replace TREAT = 1 if store_id ==  2317

	*Store ID 2327
	replace POST = 1 if year > 2012 & store_id == 2327
	replace POST = 1 if year == 2012 & month > 7 & store_id == 2327
	replace POST = 1 if year == 2012 & month == 7 & day > 10 & store_id == 2327
	replace TREAT = 1 if store_id ==  2327

	*Store ID 2560
	replace POST = 1 if year > 2012 & store_id == 2560
	replace POST = 1 if year == 2012 & month > 9 & store_id == 2560
	replace TREAT = 1 if store_id ==  2560

	*Store ID 4021
	replace POST = 1 if year > 2013 & store_id == 4021
	replace TREAT = 1 if store_id == 4021


****************************
***) Get rid of errors
****************************

	bysort POST store_id: sum paper_bag

*) Stores mislabed as having a bag fee when they do not
	tab txn_date if store_id == 976 & POST == 0 & paper_bag >0
	tab txn_date if store_id == 788 & POST == 0 & paper_bag >0
	tab txn_date if store_id == 788 & POST == 0 & paper_bag >0	
	replace paper_bag = 0 if store_id == 976 & POST == 0
	replace paper_bag = 0 if store_id == 788 & POST == 0
	replace paper_bag = 0 if store_id == 2306 & POST == 0
	
	bysort TREAT: tab store_id
	tab POST TREAT
	
*) Drop outliers 
	drop if tot_scan > 200 & (reg_nbr < 21 | sc==1)
	drop if expenditure_paid > 1000 & (reg_nbr < 21 | sc==1)
	drop if expenditure_paid < -150 & (reg_nbr < 21 | sc==1)
	
* Merge in store characteristics
	merge m:1 store_id using store_characteristics
	drop _merge

******************
*) Label Variables
******************

	label variable POST "Ban Effective Dummy"
	label variable TREAT "Treated Store Dummy"

	label variable item_qty "# Items Purchased"
	label variable expenditure_paid "Price Paid ($)"
	label variable paper_bag "Paper Bag Dummy"
	label variable reus_bag "# Resuable Bags"	
	label variable tot_scan "# Scans Made"

	label variable DEPT_1 "Txn=Alc & Tobac"
	label variable DEPT_2 "Txn=Bakery & Deli"
	label variable DEPT_3 "Txn=Dairy & Refrig"
	label variable DEPT_4 "Txn=Floral"
	label variable DEPT_5 "Txn=Food Service"
	label variable DEPT_6 "Txn=Frozen"
	label variable DEPT_7 "Txn=Meat & Sea"
	label variable DEPT_8 "Txn=Health & Other"
	label variable DEPT_9 "Txn=Produce"
	label variable DEPT_10 "Txn=Pet"
	label variable DEPT_11 "Txn=Grocery"
	label variable DEPT_12 "Txn=Baby"

	label variable sc "Reg=Self-Check"

	label variable txn_mins "Txn Time (mins)" 

	label variable cashreg_open "# Cashier lanes open"
	label variable sc_open "# Self-check lanes open"

	label variable st_bakery "Store=Bakery"
	label variable st_cust_serve "Store=Customer Service"
	label variable st_pharm "Store=Pharmacy"
	label variable st_deli "Store=Deli"
	label variable st_floral "Store=Floral"
	label variable st_coffee "Store=Coffee"
	label variable st_has_self_check "Store=SC"
	label variable st_gas "Store=Gas"
	label variable st_juice "Store=Juice"
	label variable st_sandwich "Store=Sandwich"

	label variable is_close "In-Sample store closure" 
	label variable is_remodel "In-Sample store remodel" 
	label variable store_city "Store City ID"  

	label variable remodel_date "Store Remodel Date"
	label variable open_date "Store Open Date" 
	label variable building_size "Store Building Size"
	label variable selling_size "Store Selling Size"

	drop tt1 tt2 tt3

*) Create Count Variables
	preserve
	egen dte_shift2 = group(store_id) 
			egen reg_day_id2 = group(dte_shift reg_nbr)
			
			egen cashreg_open_tag2 = tag(reg_day_id2) if reg_nbr<20 & txn_mins != .
			egen sc_open_tag2 = tag(reg_day_id2)	if ((reg_nbr > 50 & reg_nbr < 55) | reg_nbr > 92) & txn_mins != .	

			bysort dte_shift2: egen cashreg_count = sum(cashreg_open_tag2) 
			bysort dte_shift2: egen sc_count = sum(sc_open_tag2) 
			
			collapse (max) cashreg_count sc_count, by(store_id)
		save regCount.dta, replace	
	restore 

	merge m:1 store_id using regCount.dta
	drop _merge

	label variable cashreg_count "# Cashier lanes in store"
	label variable sc_count "# Self-check lanes in store"

	*) Drop if store closes in-sample (lose store 313 & 687)
		drop if is_close ==1
		drop is_close

	*) Drop if store is remodeled in-sample (lose store 910, 1196 & 2139)
		drop if is_remodel ==1
		drop is_remodel

	*) Drop 706 because their is no paper bag fee
		drop if store_id == 706 	/*Now there are 53 stores*/

	*) Fix typo in st_has_self_check
		replace st_has_self_check = 1 if store_id == 309

	*) Drop unused variables
		drop division_id DEPT_8 DEPT_5	

	save TxnDATA_53stores, replace



